package com.sinodata.bsm.cicp.collector.sqlserver;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.sinodata.bsm.cicp.collector.AbstractJDBCCollector;
import com.sinodata.bsm.cicp.exception.CollectorException;
import com.sinodata.bsm.cicp.util.DBConnectionUtil;
import com.sinodata.bsm.common.vo.ResPropertyValue;

/**
 * 
 * <p>
 * Description: 
 * </p>
 *
 * @author wangpeng
 * @version 1.0

 * <p>
 * History: 
 *
 * Date                     Author         Version     Description
 * ---------------------------------------------------------------------------------
 * 2012-8-21 下午09:20:09          wangpeng        1.0         To create
 * </p>
 *
 * @since 
 * @see
 */
public class Memory extends AbstractJDBCCollector {
    private static final Logger logger = Logger.getLogger(Memory.class);

    @Override
    public List<ResPropertyValue> collect(String ip) throws CollectorException {
        List<ResPropertyValue> values = new ArrayList<ResPropertyValue>();
        try {
            String[] propMetas = getPropMeta();
            String serverName = this.getParamMap().get("ServerName");
            String sql = "sp_getsvrinfo";
            String[] rs = this.getStrings(serverName, sql, ip);
            ResPropertyValue propertyValueX = new ResPropertyValue();
            ResPropertyValue.PropValue[] propValues = new ResPropertyValue.PropValue[propMetas.length];
            for (int i = 0; i < propMetas.length; i++) {
                propValues[i] = ResPropertyValue.createPropValue(rs[i], propMetas[i]);
            }
            propertyValueX.setValues(propValues);
            values.add(propertyValueX);
        } catch (Exception e) {
            logger.error("failed to collect sqlserver memory", e);

            throw new CollectorException(e);
        }
        return values;
    }

    private String[] getStrings(String serverName, String sql, String ip) throws CollectorException {
        String[] propValArr = new String[8];

        CallableStatement cstmt = null;
        ResultSet rst = null;
        Connection conn = null;
        try {
            conn = DBConnectionUtil.getInstance().getConnection("sqlserver", ip, serverName);
            if (conn == null) {
                return null;
            }
            float totalMem = 0.0F;
            String version = "";
            cstmt = conn.prepareCall(sql);
            cstmt.setQueryTimeout(10);
            rst = cstmt.executeQuery();
            while (rst.next()) {
                String name = rst.getString(2);
                if (name.equals("PhysicalMemory"))
                    totalMem = rst.getFloat(3);
                else if (name.equals("FileVersion"))
                    version = rst.getString(4);
            }

            if (version.contains("2000")) {
                propValArr = getCacheInfo2000(conn, totalMem);
            } else if (version.contains("2005")) {
                propValArr = getCacheInfo2005(conn, totalMem);
            }
        } catch (SQLException sqle) {
            logger.error("SQLException:" + sqle);
            throw new CollectorException(sqle);
        } catch (IOException ioe) {
            logger.error("IOException:" + ioe);
            throw new CollectorException(ioe);
        } finally {

            if (rst != null) {
                try {
                    rst.close();
                } catch (SQLException e) {
                    logger.error("failed to close result set", e);
                    throw new CollectorException(e);
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException e) {
                    logger.error("failed to close statement", e);
                    throw new CollectorException(e);
                }
            }
            DBConnectionUtil.getInstance().returnConnection(conn);
        }

        return propValArr;
    }

    @Override
    public String[] getParamMeta() {
        return new String[] { "ServerName" };
    }

    @Override
    public String[] getPropMeta() {
        //        return new String[] { "占用内存大小", "内存使用率", "Buffer缓存大小", "Procedure缓存大小", "空闲页大小", "Buffer缓存命中率", "Procedure缓存命中率", "总缓存命中率(2000以下版本)" };
        return new String[] { "MemoryOccupiedSize", "MemoryUsage", "BufferCacheSize", "ProcedureCacheSize", "FreePageSize", "BufferCacheHitRatio", "ProcedureCacheHitRatio", "TotalCacheHitRatio" };
    }

    // SQL Server 2000
    private String[] getCacheInfo2000(Connection conn, float totalMem) throws SQLException {
        String sql = "SELECT counter_name, instance_name, cntr_value FROM sysperfinfo WHERE (object_name like '%:Memory Manager%' AND counter_name = 'Total Server Memory (KB)') OR (object_name like '%:Buffer Manager%' AND counter_name = 'Procedure cache pages') OR (object_name like '%:Buffer Manager%' AND counter_name = 'Database pages') OR (object_name like '%:Buffer Manager%' AND counter_name = 'Free pages') OR (object_name like '%:Buffer Manager%' AND counter_name = 'Buffer cache hit ratio') OR (object_name like '%:Buffer Manager%' AND counter_name = 'Buffer cache hit ratio base') OR (object_name like '%:Cache Manager%' AND counter_name = 'Cache Hit Ratio' AND instance_name = 'Procedure Plans') OR (object_name like '%:Cache Manager%' AND counter_name = 'Cache Hit Ratio Base' AND instance_name = 'Procedure Plans') OR (object_name like '%:Cache Manager%' AND counter_name = 'Cache Hit Ratio' AND instance_name = '_Total') OR (object_name like + '%:Cache Manager%' AND counter_name = 'Cache Hit Ratio Base' AND instance_name = '_Total')";
        float totalCacheHitNum = 0.0F;
        float totalCacheHitBase = 0.0F;
        float procCacheHitNum = 0.0F;
        float procCacheHitBase = 0.0F;
        float usedMem = 0.0F;
        float bufferCacheSize = 0.0F;
        float procCacheSize = 0.0F;
        float freePagesSize = 0.0F;
        float bufferCacheHitNum = 0.0F;
        float bufferCacheHitBase = 0.0F;
        Statement stmt = null;
        ResultSet rst = null;
        try {
            stmt = conn.createStatement();
            stmt.setQueryTimeout(10);
            rst = stmt.executeQuery(sql);

            if (rst != null) {
                while (rst.next()) {
                    String counterName = rst.getString(1);
                    if (counterName != null)
                        counterName = counterName.trim();
                    String instanceName = rst.getString(2);
                    if (instanceName != null)
                        instanceName = instanceName.trim();
                    float counterValue = rst.getFloat(3);
                    if ("_Total".equals(instanceName)) {
                        if ("Cache Hit Ratio".equals(counterName)) {
                            totalCacheHitNum = counterValue;
                        } else if (counterName.equals("Cache Hit Ratio Base")) {
                            totalCacheHitBase = counterValue;
                        }
                    } else if (instanceName.equals("Procedure Plans")) {
                        if (counterName.equals("Cache Hit Ratio")) {
                            procCacheHitNum = counterValue;
                        } else if (counterName.equals("Cache Hit Ratio Base")) {
                            procCacheHitBase = counterValue;
                        }
                    } else {
                        if (counterName.equals("Total Server Memory (KB)")) {
                            usedMem = counterValue;
                        } else if (counterName.equals("Procedure cache pages")) {
                            procCacheSize = counterValue;
                        } else if (counterName.equals("Database pages")) {
                            bufferCacheSize = counterValue;
                        } else if (counterName.equals("Free pages")) {
                            freePagesSize = counterValue;
                        } else if (counterName.equals("Buffer cache hit ratio")) {
                            bufferCacheHitNum = counterValue;
                        } else if (counterName.equals("Buffer cache hit ratio base")) {
                            bufferCacheHitBase = counterValue;
                        }
                    }
                }
            }
        } catch (SQLException sqle) {
            logger.error("Collecting sqlserver memory information", sqle);
            throw sqle;
        } finally {
            try {
                if (rst != null)
                    rst.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        usedMem = formatDecimal(usedMem / 1024);
        float usedMemPercent = formatDecimal(usedMem / totalMem * 100);
        bufferCacheSize = formatDecimal(bufferCacheSize * 8192 / 1024 / 1024);
        procCacheSize = formatDecimal(procCacheSize * 8192 / 1024 / 1024);
        freePagesSize = formatDecimal(freePagesSize * 8192 / 1024 / 1024);
        float bufferCacheHitRatio = formatDecimal(bufferCacheHitNum / bufferCacheHitBase * 100);
        float procCacheHitRatio = formatDecimal(procCacheHitNum / procCacheHitBase * 100);
        float totalCacheHitRatio = formatDecimal(totalCacheHitNum / totalCacheHitBase * 100);
        return new String[] { "" + usedMem, "" + usedMemPercent, "" + bufferCacheSize, "" + procCacheSize, "" + freePagesSize, "" + bufferCacheHitRatio, "" + procCacheHitRatio, "" + totalCacheHitRatio, };
    }

    // SQL Server 2005
    private String[] getCacheInfo2005(Connection conn, float totalMem) throws SQLException {
        String sql = "SELECT object_name, counter_name, instance_name, cntr_value" + " FROM sysperfinfo" + " WHERE (object_name like '%Memory Manager%' AND" + " counter_name = 'Total Server Memory (KB)') OR" + " (object_name like '%Plan Cache%' AND" + " counter_name = 'Cache Pages' AND" + " instance_name = '_Total') OR" + " (object_name like '%Buffer Manager%' AND" + " counter_name = 'Database pages') OR" + " (object_name like '%Buffer Manager%' AND" + " counter_name = 'Free pages') OR" + " (object_name like '%Buffer Manager%' AND" + " counter_name = 'Buffer cache hit ratio') OR" + " (object_name like '%Buffer Manager%' AND" + " counter_name = 'Buffer cache hit ratio base') OR" + " object_name like '%Plan Cache%' AND" + " (counter_name = 'Cache Hit Ratio' AND" + " instance_name = '_Total') OR" + " (object_name like '%Plan Cache%' AND" + " counter_name = 'Cache Hit Ratio Base' AND" + " instance_name = '_Total')";
        float totalCacheHitNum = 0.0F;
        float totalCacheHitBase = 0.0F;
        float planCacheHitNum = 0.0F;
        float planCacheHitBase = 0.0F;
        float usedMem = 0.0F;
        float bufferCacheSize = 0.0F;
        float planCacheSize = 0.0F;
        float freePagesSize = 0.0F;
        float bufferCacheHitNum = 0.0F;
        float bufferCacheHitBase = 0.0F;
        Statement stmt = null;
        ResultSet rst = null;
        try {
            stmt = conn.createStatement();
            stmt.setQueryTimeout(10);
            rst = stmt.executeQuery(sql);

            if (rst != null) {
                while (rst.next()) {
                    String objectName = rst.getString(1);
                    if (objectName != null)
                        objectName = objectName.trim();
                    String counterName = rst.getString(2);
                    if (counterName != null)
                        counterName = counterName.trim();
                    String instanceName = rst.getString(3);
                    if (instanceName != null)
                        instanceName = instanceName.trim();
                    float counterValue = rst.getFloat(4);

                    if (objectName.contains("Plan Cache") && "_Total".equals(instanceName)) {
                        if ("Cache Hit Ratio".equals(counterName)) {
                            planCacheHitNum = counterValue;
                        } else if (counterName.equals("Cache Hit Ratio Base")) {
                            planCacheHitBase = counterValue;
                        } else if (counterName.equals("Cache Pages")) {
                            planCacheSize = counterValue;
                        }
                    } else {
                        if (counterName.equals("Total Server Memory (KB)")) {
                            usedMem = counterValue;
                        } else if (counterName.equals("Database pages")) {
                            bufferCacheSize = counterValue;
                        } else if (counterName.equals("Free pages")) {
                            freePagesSize = counterValue;
                        } else if (counterName.equals("Buffer cache hit ratio")) {
                            bufferCacheHitNum = counterValue;
                        } else if (counterName.equals("Buffer cache hit ratio base")) {
                            bufferCacheHitBase = counterValue;
                        }
                    }
                }
            }
        } catch (SQLException sqle) {
            logger.error("Collecting sqlserver memory information", sqle);
            throw sqle;
        } finally {
            try {
                if (rst != null)
                    rst.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        usedMem = formatDecimal(usedMem / 1024);
        float usedMemPercent = formatDecimal(usedMem / totalMem * 100);
        bufferCacheSize = formatDecimal(bufferCacheSize * 8192 / 1024 / 1024);
        planCacheSize = formatDecimal(planCacheSize * 8192 / 1024 / 1024);
        freePagesSize = formatDecimal(freePagesSize * 8192 / 1024 / 1024);
        float bufferCacheHitRatio = formatDecimal(bufferCacheHitNum / bufferCacheHitBase * 100);
        float procCacheHitRatio = formatDecimal(planCacheHitNum / planCacheHitBase * 100);
        float totalCacheHitRatio = formatDecimal(totalCacheHitNum / totalCacheHitBase * 100);// 没有意义对于2005
        return new String[] { "" + usedMem, "" + usedMemPercent, "" + bufferCacheSize, "" + planCacheSize, "" + freePagesSize, "" + bufferCacheHitRatio, "" + procCacheHitRatio, "" + totalCacheHitRatio };
    }

    private float formatDecimal(float num) {
        DecimalFormat fmt = new DecimalFormat("#.00");
        try {
            num = Float.parseFloat(fmt.format(num));
        } catch (NumberFormatException e) {
            return 0.0F;
        }
        return num;
    }
}
